﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace SharpSoft.Data.DAOS
{
    public class CodeDicDAO : DAO
    {
        public CodeDicDAO(Database p_db, int p_userid) : base(p_db, p_userid)
        {
        }

        protected override string InitScriptFileName => "codedic.txt";

        /// <summary>
        /// 获取全部的代码字典
        /// </summary>
        /// <returns></returns>
        public CodeDicModel[] GetAllCodeDic()
        {
            var sql = "SELECT * FROM `CodeDic`;";
            return this.db.ExecuteArray<CodeDicModel>(sql);
        }
        /// <summary>
        /// 获取代码字典的键值列表
        /// </summary>
        /// <param name="dicname"></param>
        /// <returns></returns>
        public Dictionary<string, string> GetCodeDic(string dicname)
        {
            var dicid = GetCodeDicId(dicname);
            if (dicid.HasValue)
            {
                var sql = "SELECT `dickey`,`dicvalue` FROM `CodeDic_Values` WHERE `dicid`=@dicid;";
                var dt = db.ExecuteDataTable(sql, null, new { dicid });
                var dic = new Dictionary<string, string>();
                foreach (DataRow item in dt.Rows)
                {
                    dic.Add(item["dickey"].ToString(), item["dicvalue"].ToString());
                }
                return dic;
            }
            else
            {
                throw new Exception($"不存在代码字典：[{dicname}]");
            }

        }
        /// <summary>
        /// 是否存在指定名称的代码字典
        /// </summary>
        /// <param name="dicname"></param>
        /// <returns></returns>
        public bool ExistsCodeDic(string dicname)
        {
            var sql = "SELECT COUNT(dicid) FROM `CodeDic` WHERE `dicname`=@dicname;";
            var count = db.ExecuteInt32(sql, new { dicname });
            return count > 0;
        }
        /// <summary>
        /// 获取代码字典的Id
        /// </summary>
        /// <param name="dicname"></param>
        /// <returns></returns>
        public int? GetCodeDicId(string dicname)
        {
            var sql = "SELECT `dicid` FROM `CodeDic` WHERE `dicname`=@dicname";
            var id = db.ExecuteScalar<int?>(sql, new { dicname });
            return id;
        }
        /// <summary>
        /// 获取指定代码字典中指定键的值
        /// </summary>
        /// <param name="dicname"></param>
        /// <param name="dickey"></param>
        /// <returns></returns>
        public string GetCodeDicValue(string dicname, string dickey)
        {
            var dicid = GetCodeDicId(dicname);
            if (dicid.HasValue)
            {
                var sql = "SELECT `dicvalue` FROM `CodeDic_Values` WHERE `dicid`=@dicid AND `dickey`=@dickey;";
                return db.ExecuteString(sql, new { dicid, dickey });
            }
            return null;
        }
        /// <summary>
        /// 插入代码字典并返回其自动id
        /// </summary>
        /// <param name="dicname"></param>
        /// <param name="display"></param>
        /// <param name="remark"></param>
        /// <returns></returns>
        public int InsertCodeDic(string dicname, string display = null, string remark = null)
        {
            var sql = "INSERT INTO `CodeDic`(`dicname`,`display`,`remark`)VALUES(@dicname,@display,@remark);SELECT LAST_INSERT_ID();";
            return db.ExecuteInt32(sql, new { dicname, display, remark });
        }
        /// <summary>
        /// 插入代码字典值
        /// </summary>
        /// <param name="dicid"></param>
        /// <param name="dickey"></param>
        /// <param name="dicvalue"></param>
        /// <param name="display"></param>
        /// <param name="remark"></param>
        /// <returns></returns>
        public void InsertCodeDicValue(string dicid, string dickey, string dicvalue, string display = null, string remark = null)
        {
            var sql = "INSERT INTO `CodeDic_Values`(`dicid`,`dickey`,`dicvalue`,`display`,`remark`)VALUES(@dicname,@display,@remark);";
            db.ExecuteNonQuery(sql, new { dicid, dickey, dicvalue, display, remark });
        }
    }
    /// <summary>
    /// 代码字典
    /// </summary>
    public class CodeDicModel
    {
        public int dicid { get; set; }
        public string dicname { get; set; }
        public string display { get; set; }
        public string remark { get; set; }
    }
}
